USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[SigVal]    Script Date: 06/04/2014 15:58:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SigVal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SigVal]
GO

CREATE PROCEDURE [dbo].[SigVal]
	@TAIKAINO int,
	@KYOGINO int,
	@KUMINO int,
	@ROUNDNO int,
	@SUBNO int,
	@SYUMOKUKUBUN int,
	@EXE2FLG int
AS
SELECT *
FROM
(	SELECT DISTINCT 
		RS.KIROKU, RS.SIGINO,
		CASE WHEN RS.KIROKU = 0 THEN '' ELSE dbo.Kiroku(RS.KIROKU, @SYUMOKUKUBUN, 3
		,(CASE WHEN ISNUMERIC(RE.EXTEND2)  = 1 then RE.EXTEND2 else 3 END)
		)END AS SIGIVAL
	FROM TBL_RESULT_SIGI AS RS,
		TBL_KUMI AS TKU, 
		TBL_RESULT AS RE 
	WHERE RS.TAIKAINO = @TAIKAINO 
		AND RS.KYOGINO = @KYOGINO 
		AND RS.ROUNDNO = @ROUNDNO 
		AND RS.SUBNO = @SUBNO 
		AND RS.TAIKAINO = @TAIKAINO
		AND RS.KYOGINO = RE.KYOGINO 
		AND RS.SUBNO = RE.SUBNO 
		AND RS.ROUNDNO = RE.ROUNDNO 
		AND RS.KUMINO = @KUMINO
		AND RS.KUMINO = RE.KUMINO 
		AND RS.LANE = RE.LANE
		AND TKU.TAIKAINO = @TAIKAINO 
		AND TKU.KYOGINO = @KYOGINO 
		AND TKU.SUBNO = @SUBNO 
		AND TKU.ROUNDNO = @ROUNDNO
		AND RS.EXTEND2 LIKE CASE WHEN @EXE2FLG = 0 THEN '' ELSE '1' END
)AS RS
	ORDER BY CASE WHEN @EXE2FLG = 0 THEN RS.KIROKU  ELSE RS.SIGINO END
RETURN 0
GO

